package com.cindata

import com.cindata.dictionary.*
import grails.gorm.transactions.Transactional
import org.springframework.beans.factory.InitializingBean
import org.springframework.beans.factory.annotation.Value

import java.sql.Connection
import java.sql.DriverManager
import java.sql.ResultSet
import java.sql.ResultSetMetaData
import java.sql.Statement

@Transactional
class JdbcService
{
    static scope = "singleton"
    @Value('${dataSource.username}')
    String username
    @Value('${dataSource.password}')
    String password
    @Value('${dataSource.url}')
    String url


    def getConnection(){
        def connection
        Class.forName("org.postgresql.Driver")
        connection =  DriverManager.getConnection(url, username, password)
        return connection
    }

    def update(String sql){
        long start = System.currentTimeMillis()
        Connection connection  = getConnection()
        Statement statement = connection.createStatement()
        try{
            statement.execute(sql)
        }catch(Exception e){
            println e.getMessage()
        }finally{
            statement.close()
            connection.close()
            long end = System.currentTimeMillis();
            println   "use:"+(end-start)+"ms."
        }
    }

    def batchUpdate(List<String> sqls){
        long start = System.currentTimeMillis();
        Connection connection  = getConnection()
        Statement statement = connection.createStatement()
        connection.setAutoCommit(false)
        try{
            for(String s : sqls){
                statement.addBatch(s)
            }
            statement.executeBatch()
            connection.commit()
        }catch(Exception e){
            println e.getMessage()
        }finally{
            statement.close()
            connection.close()
            long end = System.currentTimeMillis();
            println "size:"+sqls.size()+"  use:"+(end-start)+"ms."
        }
    }


    def findBySql(String sql)throws Exception{
        System.out.println(sql);
        Connection connection  = getConnection();
        Statement statement = connection.createStatement();
        ResultSet rs=statement.executeQuery(sql);
        List<HashMap<String,String>> rList = new ArrayList<HashMap<String,String>>();
        try{
            ResultSetMetaData rsMeta=rs.getMetaData();
            int columnCount=rsMeta.getColumnCount();
            while(rs.next()){
                HashMap<String,String> map = new HashMap<String,String>();
                for (int i=1; i<=columnCount; i++) {
                    map.put(rsMeta.getColumnLabel(i),rs.getString(i));
                }
                rList.add(map);
            }
        }catch(Exception e){

        }finally{
            rs.close();
            statement.close();
        }
        return rList;
    }


}
